import express from 'express';
import { query } from '../config/database.js';

const router = express.Router();

// 获取成绩列表，支持按比赛id、轮次和魔方类型过滤
router.get('/scores', async (req, res) => {
  try {
    const { match_id, round_number, cube_type_id, is_confirmed, school_id } = req.query;
    
    let queryStr = `
      SELECT ms.*, 
             p.name as player_name, 
             s.name as school_name,
             s.id as school_id,
             ct.name as cube_type_name,
             COALESCE(ms.draw_number, mt.table_number) as draw_number
      FROM match_scores ms
      JOIN players p ON ms.player_id = p.id
      JOIN schools s ON p.school_id = s.id
      JOIN cube_types ct ON ms.cube_type_id = ct.id
      LEFT JOIN match_tables mt ON ms.match_id = mt.match_id 
                               AND ms.cube_type_id = mt.cube_type_id 
                               AND ms.round_number = mt.round_number 
                               AND ms.player_id IN (mt.player1_id, mt.player2_id)
      WHERE 1=1
    `;
    
    const params = [];
    
    if (match_id) {
      queryStr += ' AND ms.match_id = ?';
      params.push(match_id);
    }
    
    if (round_number) {
      queryStr += ' AND ms.round_number = ?';
      params.push(round_number);
    }
    
    if (cube_type_id) {
      queryStr += ' AND ms.cube_type_id = ?';
      params.push(cube_type_id);
    }
    
    if (is_confirmed !== undefined) {
      queryStr += ' AND ms.is_confirmed = ?';
      params.push(is_confirmed);
    }
    
    if (school_id) {
      queryStr += ' AND s.id = ?';
      params.push(school_id);
    }
    
    queryStr += ' ORDER BY COALESCE(ms.draw_number, mt.table_number) ASC, ms.created_at DESC';
    
    const scores = await query(queryStr, params);
    res.json(scores);
  } catch (error) {
    console.error('Error fetching scores:', error);
    res.status(500).json({ error: '获取成绩失败' });
  }
});

// 获取单个成绩
router.get('/scores/:id', async (req, res) => {
  try {
    const score = await query(
      `SELECT ms.*, 
              p.name as player_name, 
              s.name as school_name,
              ct.name as cube_type_name
       FROM match_scores ms
       JOIN players p ON ms.player_id = p.id
       JOIN schools s ON p.school_id = s.id
       JOIN cube_types ct ON ms.cube_type_id = ct.id
       WHERE ms.id = ?`, 
      [req.params.id]
    );
    
    if (score.length === 0) {
      return res.status(404).json({ error: '成绩不存在' });
    }
    
    res.json(score[0]);
  } catch (error) {
    console.error('Error fetching score:', error);
    res.status(500).json({ error: '获取成绩失败' });
  }
});

// 添加新成绩
router.post('/scores', async (req, res) => {
  try {
    const { match_id, player_id, cube_type_id, round_number, score, is_completed, is_confirmed } = req.body;
    
    // 检查必填字段
    if (!match_id || !player_id || !cube_type_id || !round_number) {
      return res.status(400).json({ error: '缺少必要参数' });
    }
    
    // 检查是否已存在该记录
    const existingScores = await query(
      'SELECT * FROM match_scores WHERE match_id = ? AND player_id = ? AND cube_type_id = ? AND round_number = ?',
      [match_id, player_id, cube_type_id, round_number]
    );
    
    if (existingScores.length > 0) {
      return res.status(400).json({ error: '该成绩记录已存在，请使用更新接口' });
    }
    
    // 插入新记录
    const result = await query(
      'INSERT INTO match_scores (match_id, player_id, cube_type_id, round_number, score, is_completed, is_confirmed) VALUES (?, ?, ?, ?, ?, ?, ?)',
      [match_id, player_id, cube_type_id, round_number, score, is_completed || 0, is_confirmed || 0]
    );
    
    // 获取新插入的记录
    const newScore = await query('SELECT * FROM match_scores WHERE id = ?', [result.insertId]);
    
    res.status(201).json(newScore[0]);
  } catch (error) {
    console.error('Error creating score:', error);
    res.status(500).json({ error: '添加成绩失败' });
  }
});

// 更新成绩
router.put('/scores/:id', async (req, res) => {
  try {
    const { score, is_completed, is_confirmed } = req.body;
    const scoreId = req.params.id;
    
    // 检查成绩是否存在
    const existingScores = await query('SELECT * FROM match_scores WHERE id = ?', [scoreId]);
    
    if (existingScores.length === 0) {
      return res.status(404).json({ error: '成绩不存在' });
    }
    
    // 构建更新语句
    let updates = [];
    let params = [];
    
    if (score !== undefined) {
      updates.push('score = ?');
      params.push(score);
    }
    
    if (is_completed !== undefined) {
      updates.push('is_completed = ?');
      params.push(is_completed);
    }
    
    if (is_confirmed !== undefined) {
      updates.push('is_confirmed = ?');
      params.push(is_confirmed);
    }
    
    // 如果没有要更新的字段
    if (updates.length === 0) {
      return res.status(400).json({ error: '没有提供要更新的字段' });
    }
    
    params.push(scoreId);
    
    // 执行更新
    await query(
      `UPDATE match_scores SET ${updates.join(', ')} WHERE id = ?`,
      params
    );
    
    // 获取更新后的记录
    const updatedScore = await query('SELECT * FROM match_scores WHERE id = ?', [scoreId]);
    
    res.json(updatedScore[0]);
  } catch (error) {
    console.error('Error updating score:', error);
    res.status(500).json({ error: '更新成绩失败' });
  }
});

// 删除成绩
router.delete('/scores/:id', async (req, res) => {
  try {
    const scoreId = req.params.id;
    
    // 检查成绩是否存在
    const existingScores = await query('SELECT * FROM match_scores WHERE id = ?', [scoreId]);
    
    if (existingScores.length === 0) {
      return res.status(404).json({ error: '成绩不存在' });
    }
    
    // 执行删除
    await query('DELETE FROM match_scores WHERE id = ?', [scoreId]);
    
    res.json({ message: '成绩已删除' });
  } catch (error) {
    console.error('Error deleting score:', error);
    res.status(500).json({ error: '删除成绩失败' });
  }
});

// 批量确认成绩
router.post('/scores/batch-confirm', async (req, res) => {
  try {
    const { match_id, round_number, cube_type_id } = req.body;
    
    if (!match_id) {
      return res.status(400).json({ error: '缺少比赛ID参数' });
    }
    
    // 构建更新语句
    let whereClause = 'match_id = ?';
    let params = [match_id];
    
    if (round_number) {
      whereClause += ' AND round_number = ?';
      params.push(round_number);
    }
    
    if (cube_type_id) {
      whereClause += ' AND cube_type_id = ?';
      params.push(cube_type_id);
    }
    
    // 执行批量更新
    const result = await query(
      `UPDATE match_scores SET is_confirmed = 1 WHERE ${whereClause} AND is_confirmed = 0`,
      params
    );
    
    res.json({
      message: '批量确认成功',
      count: result.affectedRows
    });
  } catch (error) {
    console.error('Error batch confirming scores:', error);
    res.status(500).json({ error: '批量确认成绩失败' });
  }
});

// 获取比赛的轮次列表
router.get('/matches/:id/scores/rounds', async (req, res) => {
  try {
    const matchId = req.params.id;
    
    const rounds = await query(
      'SELECT DISTINCT round_number FROM match_scores WHERE match_id = ? ORDER BY round_number',
      [matchId]
    );
    
    res.json(rounds.map(r => r.round_number));
  } catch (error) {
    console.error('Error fetching rounds:', error);
    res.status(500).json({ error: '获取轮次列表失败' });
  }
});

// 获取比赛的魔方类型列表
router.get('/matches/:id/scores/cube-types', async (req, res) => {
  try {
    const matchId = req.params.id;
    
    const cubeTypes = await query(
      `SELECT DISTINCT ct.id, ct.name 
       FROM match_scores ms
       JOIN cube_types ct ON ms.cube_type_id = ct.id
       WHERE ms.match_id = ?`,
      [matchId]
    );
    
    res.json(cubeTypes);
  } catch (error) {
    console.error('Error fetching cube types:', error);
    res.status(500).json({ error: '获取魔方类型列表失败' });
  }
});

export default router; 